﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace ParkingLot.Utility.DBConnection
{
    /// <summary>
    /// MySql的帮助类
    /// </summary>
    public class MySqlHelper
    {
        public static void InsertData(string connectionString, DataTable dataTable)
        {
            MySqlConnection connection = null;
            try
            {
                // 拼写查询语句
                string queryString = GetInsertString(dataTable);
                // 建立连接
                connection = new MySqlConnection(connectionString);
                connection.Open();
                // 执行查询语句
                MySqlCommand command = new MySqlCommand(queryString, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                Debug.WriteLine(exception.Message);
            }
            finally
            {
                // 断开连接，避免连接数只增不减
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public static void InsertDataThread(string connectionString, DataTable dataTable, int threadTimes)
        {
            MySqlConnection connection = null;
            try
            {
                // 拼写查询语句
                string queryString = GetInsertString(dataTable);
                // 建立连接
                connection = new MySqlConnection(connectionString);
                List<DataTable> lisDatatabel = new List<DataTable>();
                int threadCount = (dataTable.Rows.Count - 1) / threadTimes;
                for (int i = 0; i < threadTimes; i++)
                {
                    DataTable dt = dataTable.Clone();
                    for (int j = 0; j < threadCount; j++)
                    {
                        dt.ImportRow(dataTable.Rows[i * threadCount + j]);
                    }
                    lisDatatabel.Add(dt);
                }
                DataTable lastTable = dataTable.Clone();
                //最后
                for (int i = threadCount * threadTimes; i < dataTable.Rows.Count; i++)
                {
                    lastTable.ImportRow(dataTable.Rows[i]);
                }
                lisDatatabel.Add(lastTable);

                for (int i = 0; i < lisDatatabel.Count; i++)
                {
                    ThreadMySqlData threadMySqlData = new ThreadMySqlData();
                    threadMySqlData.CurThreadTable = lisDatatabel[i];
                    threadMySqlData.Index = i;
                    ThreadHelp.Instance.lisIsFinish.Add(false);
                    ParameterizedThreadStart threadStart = new ParameterizedThreadStart(GetQueryString);
                    Thread thread = new Thread(threadStart);
                    thread.Start(threadMySqlData);
                }

                bool bolIsFinish = GetListYu(ThreadHelp.Instance.lisIsFinish);
                while (bolIsFinish == false)
                {
                    Thread.Sleep(200);
                    bolIsFinish = GetListYu(ThreadHelp.Instance.lisIsFinish);
                }
                connection.Open();
                foreach (var item in ThreadHelp.Instance.dictInsertString.Values)
                {
                    // 执行查询语句
                    MySqlCommand command = new MySqlCommand(item, connection);
                    command.ExecuteNonQuery();
                }

            }
            catch (Exception exception)
            {
                Debug.WriteLine(exception.Message);
            }
            finally
            {
                ThreadHelp.Instance.clear();
                // 断开连接，避免连接数只增不减
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 插入数据（多线程）
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="dataTable">表</param>
        /// <param name="threadTimes">线程个数</param>
        public static void InserDataThread(string connectionString, DataTable dataTable, int threadTimes)
        {
            MySqlConnection connection = null;
            try
            {
                // 建立连接
                connectionString += "Allow User variables=true;";
                List<DataTable> lisDatatabel = new List<DataTable>();
                int threadCount = (dataTable.Rows.Count - 1) / threadTimes;
                for (int i = 0; i < threadTimes; i++)
                {
                    DataTable dt = dataTable.Clone();
                    for (int j = 0; j < threadCount; j++)
                    {
                        dt.ImportRow(dataTable.Rows[i * threadCount + j]);
                    }
                    lisDatatabel.Add(dt);
                }
                DataTable lastTable = dataTable.Clone();
                //最后
                for (int i = threadCount * threadTimes; i < dataTable.Rows.Count; i++)
                {
                    lastTable.ImportRow(dataTable.Rows[i]);
                }
                lisDatatabel.Add(lastTable);

                for (int i = 0; i < lisDatatabel.Count; i++)
                {



                    ThreadMySqlData threadMySqlData = new ThreadMySqlData();
                    threadMySqlData.connection = new MySqlConnection(connectionString);
                    threadMySqlData.connection.Open();
                    threadMySqlData.CurThreadTable = lisDatatabel[i];
                    threadMySqlData.Index = i;
                    ThreadHelp.Instance.lisIsFinish.Add(false);
                    ParameterizedThreadStart threadStart = new ParameterizedThreadStart(InsertData);
                    Thread thread = new Thread(threadStart);
                    thread.Start(threadMySqlData);
                }

                bool bolIsFinish = GetListYu(ThreadHelp.Instance.lisIsFinish);
                while (bolIsFinish == false)
                {
                    Thread.Sleep(300);
                    bolIsFinish = GetListYu(ThreadHelp.Instance.lisIsFinish);
                }
                int p = 0;
                //// 拼写查询语句
                //string queryString = GetInsertString(dataTable);
                //// 执行查询语句
                //MySqlCommand command = new MySqlCommand(queryString, connection);
                //command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                Debug.WriteLine(exception.Message);
            }
            finally
            {
                ThreadHelp.Instance.clear();
                // 断开连接，避免连接数只增不减
                if (connection != null)
                {
                    //connection.Close();
                }
            }
        }

        private static bool GetListYu(List<bool> lisBolValues)
        {
            bool bolResult = true;
            foreach (bool value in lisBolValues)
            {
                bolResult = bolResult && value;
            }
            return bolResult;
        }

        private static void GetQueryString(object obj)
        {
            ThreadMySqlData threadMySql = obj as ThreadMySqlData;

            // 拼写查询语句
            string queryString = GetInsertString(threadMySql.CurThreadTable);
            // 执行查询语句
            ThreadHelp.Instance.dictInsertString.Add(threadMySql.Index, queryString);
            ThreadHelp.Instance.lisIsFinish[threadMySql.Index] = true;
        }

        private static void InsertData(object obj)
        {
            ThreadMySqlData threadMySql = obj as ThreadMySqlData;
            try
            {

                // 拼写查询语句
                string queryString = GetInsertString(threadMySql.CurThreadTable);
                // 执行查询语句

                MySqlCommand command = new MySqlCommand(queryString, threadMySql.connection);
                command.ExecuteNonQuery();
                command.CommandTimeout = 0;
                ThreadHelp.Instance.lisIsFinish[threadMySql.Index] = true;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (threadMySql.connection != null)
                {
                    threadMySql.connection.Close();
                }
            }
        }

        #region 外部方法

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="server">服务器</param>
        /// <param name="port">端口号</param>
        /// <param name="dbName">数据库名称</param>
        /// <param name="user">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>连接字符串</returns>
        public string GetConnectionString(string server, string port, string dbName, string user, string password)
        {
            string result = string.Format("server={0};port={1};database={2};user id={3};password={4};", server, port, dbName, user, password);
            return result;
        }

        #endregion

        #region 内部方法

        private static string GetColumnListString(List<string> columnNameList)
        {
            string result = "";
            for (int i = 0; i < columnNameList.Count; i++)
            {
                string columnName = columnNameList[i];
                result += string.Format("`{0}`", columnName);
                if (i != columnNameList.Count - 1)
                {
                    // 如果当前循环不是最后一次，应该添加", "分隔符
                    result += ", ";
                }
            }
            return result;
        }

        private static string GetValueString(object value)
        {
            if (value == null)
            {
                return "";
            }
            if (value is DateTime)
            {
                DateTime time = (DateTime)value;
                string timeString = string.Format(
                    "'{0:0000}-{1:00}-{2:00} {3:00}:{4:00}:{5:00}'",
                    time.Year, time.Month, time.Day, time.Hour, time.Minute, time.Second);
                return timeString;
            }
            string valueString = value.ToString();
            return valueString;
        }

        private static string GetValueListString(DataRow row, List<string> columnNameList)
        {
            string result = "";
            for (int i = 0; i < columnNameList.Count; i++)
            {
                string columnName = columnNameList[i];
                object columnValue = row[columnName];
                string columnValueString = GetValueString(columnValue);
                result += string.Format("'{0}'", columnValueString);
                if (i != columnNameList.Count - 1)
                {
                    // 如果当前循环不是最后一次，应该添加", "分隔符
                    result += ", ";
                }
            }
            return result;
        }

        private static string GetRowListString2(DataTable table, List<string> columnNameList)
        {
            StringBuilder result = new StringBuilder();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                string rowString = GetValueListString(row, columnNameList);
                result.Append("(" + rowString + ")");
                if (i != table.Rows.Count - 1)
                {
                    // 如果当前循环不是最后一次，应该添加", "分隔符
                    result.Append(", ");
                }
            }
            return result.ToString();
        }

        private static string GetRowListString(DataTable table, List<string> columnNameList)
        {
            string result = "";
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                string rowString = GetValueListString(row, columnNameList);
                result += string.Format("({0})", rowString);
                if (i != table.Rows.Count - 1)
                {
                    // 如果当前循环不是最后一次，应该添加", "分隔符
                    result += ", ";
                }
            }
            return result;
        }

        private static string GetInsertString(DataTable table)
        {
            // 表名
            string tableName = table.TableName;
            // 列名集合
            List<string> columnNameList = (from DataColumn eachColumn in table.Columns select eachColumn.ColumnName).ToList();
            string columnListString = GetColumnListString(columnNameList);
            string rowListString = GetRowListString2(table, columnNameList);
            // 查询语句
            string queryString = string.Format("insert into `{0}` ({1}) values {2}", tableName, columnListString, rowListString);
            return queryString;
        }

        #endregion
    }

    public class ThreadHelp
    {
        private ThreadHelp()
        {

        }

        #region 单例
        private static ThreadHelp m_Instance = null;

        private static object m_Locker = new object();

        public static ThreadHelp Instance
        {
            get
            {
                if (m_Instance == null)
                {
                    lock (m_Locker)
                    {
                        if (m_Instance == null)
                        {
                            m_Instance = new ThreadHelp();
                        }
                    }
                }
                return m_Instance;
            }

        }
        #endregion

        public MySqlConnection connection;

        public List<bool> lisIsFinish = new List<bool>();

        public Dictionary<int, string> dictInsertString = new Dictionary<int, string>();

        public void clear()
        {
            this.dictInsertString.Clear();
            this.lisIsFinish.Clear();
        }

    }

    public class ThreadMySqlData
    {
        public MySqlConnection connection;

        public DataTable CurThreadTable;

        public int Index;
    }
}
